package com.dpteam.dao.impl;

import com.dpteam.dao.ImportProductDao;
import com.dpteam.model.ImportProduct;
import com.dpteam.util.Page;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.stereotype.Repository;

import java.text.DecimalFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.Month;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * User: luolei
 * Date: 2016/1/8
 * Time: 8:37
 */
@Repository
public class ImportProductDaoImpl extends BaseDaoImpl<ImportProduct> implements ImportProductDao {

    private static DecimalFormat df=new java.text.DecimalFormat("#.##");

    @Override
    public List<ImportProduct> findByUserId(int userId) {
        String hql = "from ImportProduct where userId = ? order by id ";
        return find(hql, userId);
    }

    @Override
    public List<ImportProduct> findByUserIdByPage(int userId, Page page) {
        String hql = "from ImportProduct where userId = ? order by id ";
        return find(hql, page, userId);
    }

    @Override
    public List<ImportProduct> findByProductId(int productId) {
        String hql = "from ImportProduct where productId = ? order by id ";
        return find(hql, productId);
    }

    @Override
    public List<ImportProduct> findByProductIdByPage(int productId, Page page) {
        String hql = "from ImportProduct where productId = ? order by id ";
        return find(hql, page, productId);
    }

    @Override
    public List<ImportProduct> findAll() {
        String hql = "from ImportProduct order by id ";
        return find(hql);
    }

    @Override
    public List<ImportProduct> findAllByPage(Page page) {
        String hql = "from ImportProduct order by id";
        return find(hql, page);
    }

    @Override
    public List<ImportProduct> findByYear(int year) {
        LocalDate begin = LocalDate.of(year, 1, 1);
        LocalDate end = LocalDate.of(year, 12, 31);
        return findBetween(begin, end);
    }

    @Override
    public List<ImportProduct> findByYearByPage(int year, Page page) {
        LocalDate begin = LocalDate.of(year, 1, 1);
        LocalDate end = LocalDate.of(year, 12, 31);
        return findBetweenByPage(begin, end, page);
    }

    @Override
    public List<ImportProduct> findByMonth(int month) {
        LocalDate now = LocalDate.now();
        Month monthNow = Month.of(month);
        LocalDate begin = now.withMonth(month).withDayOfMonth(1);
        LocalDate end = now.withMonth(month).withDayOfMonth(monthNow.maxLength());
        return findBetween(begin, end);
    }

    @Override
    public List<ImportProduct> findByMonthByPage(int month, Page page) {
        LocalDate now = LocalDate.now();
        Month monthNow = Month.of(month);
        LocalDate begin = now.withMonth(month).withDayOfMonth(1);
        LocalDate end = now.withMonth(month).withDayOfMonth(monthNow.maxLength());
        return findBetweenByPage(begin, end, page);
    }

    @Override
    public List<ImportProduct> findByYearAndMonth(int year, int month) {
        Month monthNow = Month.of(month);
        LocalDate begin = LocalDate.of(year, month, 1);
        LocalDate end = LocalDate.of(year, month, monthNow.maxLength());
        return findBetween(begin, end);
    }

    @Override
    public List<ImportProduct> findByYearAndMonthByPage(int year, int month, Page page) {
        Month monthNow = Month.of(month);
        LocalDate begin = LocalDate.of(year, month, 1);
        LocalDate end = LocalDate.of(year, month, monthNow.maxLength());
        return findBetweenByPage(begin, end, page);
    }

    @Override
    public List<ImportProduct> findBetween(LocalDate begin, LocalDate end) {
        LocalDateTime beginDateTime = LocalDateTime.of(begin, LocalTime.now());
        LocalDateTime endDateTime = LocalDateTime.of(end, LocalTime.now());
        String hql = "from ImportProduct where updateTime <= ? and updateTime >= ? order by id ";
        return find(hql, endDateTime, beginDateTime);
    }

    @Override
    public List<ImportProduct> findBetweenByPage(LocalDate begin, LocalDate end, Page page) {
        LocalDateTime beginDateTime = LocalDateTime.of(begin, LocalTime.now());
        LocalDateTime endDateTime = LocalDateTime.of(end, LocalTime.now());
        String hql = "from ImportProduct where updateTime <= ? and updateTime >= ? order by id ";
        return find(hql, page, endDateTime, beginDateTime);
    }

    @Override
    public void deleteById(int id) {
        ImportProduct importProduct = get(ImportProduct.class, id);
        if (importProduct != null) {
            delete(importProduct);
        }
    }

    @Override
    public void batchInsert(List<ImportProduct> list) {
        Session session = getCurrentSession();
        for (int i = 0; i < list.size(); i++) {
            session.save(list.get(i));
            if (i % 20 == 0) {
                session.flush();
                session.clear();
            }
        }
    }

    @Override
    public int countAmount(int year) {
        LocalDateTime begin = LocalDateTime.of(LocalDate.of(year, 1, 1), LocalTime.MIN);
        LocalDateTime end = LocalDateTime.of(LocalDate.of(year, 12, 31), LocalTime.MAX);
        String hql = "select sum(amount) from ImportProduct where updateTime >= ? and updateTime <= ?";
        Query query = getCurrentSession().createQuery(hql);
        query.setParameter(0, begin);
        query.setParameter(1, end);
        Long result = (Long)query.uniqueResult();
        return result == null ? 0 : result.intValue();
    }

    @Override
    public int countAmount(int year, int month) {
        Month month1 = Month.of(month);
        LocalDate localDate = LocalDate.of(year, month, 1);
        int maxDay = month1.maxLength();
        if (!localDate.isLeapYear() && month == 2) {
            maxDay--;
        }
        LocalDateTime begin = LocalDateTime.of(LocalDate.of(year, month, 1), LocalTime.MIN);
        LocalDateTime end = LocalDateTime.of(LocalDate.of(year, month, maxDay), LocalTime.MAX);
        String hql = "select sum(amount) from ImportProduct where updateTime >= ? and updateTime <= ?";
        Query query = getCurrentSession().createQuery(hql);
        query.setParameter(0, begin);
        query.setParameter(1, end);
        Long result = (Long)query.uniqueResult();
        return result == null ? 0 : result.intValue();
    }

    @Override
    public int countAmount(int year, int month, int categoryId) {
        Month month1 = Month.of(month);
        LocalDate localDate = LocalDate.of(year, month, 1);
        int maxDay = month1.maxLength();
        if (!localDate.isLeapYear() && month == 2) {
            maxDay--;
        }
        LocalDateTime begin = LocalDateTime.of(LocalDate.of(year, month, 1), LocalTime.MIN);
        LocalDateTime end = LocalDateTime.of(LocalDate.of(year, month, maxDay), LocalTime.MAX);
        String hql = "select sum(amount) from ImportProduct where updateTime >= ? and updateTime <= ? and product.category.id = ?";
        Query query = getCurrentSession().createQuery(hql);
        query.setParameter(0, begin);
        query.setParameter(1, end);
        query.setParameter(2, categoryId);
        Long result = (Long)query.uniqueResult();
        return result == null? 0 : result.intValue();
    }

    @Override
    public double countCost(int year) {
        LocalDateTime begin = LocalDateTime.of(LocalDate.of(year, 1, 1), LocalTime.MIN);
        LocalDateTime end = LocalDateTime.of(LocalDate.of(year, 12, 31), LocalTime.MAX);
        String hql = "select sum(total) from ImportProduct where updateTime >= ? and updateTime <= ?";
        Query query = getCurrentSession().createQuery(hql);
        query.setParameter(0, begin);
        query.setParameter(1, end);
        Double result = (Double)query.uniqueResult();
//        return result == null ? 0 : result.doubleValue();
        return result == null ? 0 : Double.parseDouble(df.format(result));
    }

    @Override
    public double countCost(int year, int month) {
        Month month1 = Month.of(month);
        LocalDate localDate = LocalDate.of(year, month, 1);
        int maxDay = month1.maxLength();
        if (!localDate.isLeapYear() && month == 2) {
            maxDay--;
        }
        LocalDateTime begin = LocalDateTime.of(LocalDate.of(year, month, 1), LocalTime.MIN);
        LocalDateTime end = LocalDateTime.of(LocalDate.of(year, month, maxDay), LocalTime.MAX);
        String hql = "select sum(total) from ImportProduct where updateTime >= ? and updateTime <= ?";
        Query query = getCurrentSession().createQuery(hql);
        query.setParameter(0, begin);
        query.setParameter(1, end);
        Double result = (Double)query.uniqueResult();
//        return result == null ? 0 :  result.doubleValue();
        return result == null ? 0 : Double.parseDouble(df.format(result));
    }

    @Override
    public double countCost(int year, int month, int categoryId) {
        Month month1 = Month.of(month);
        LocalDate localDate = LocalDate.of(year, month, 1);
        int maxDay = month1.maxLength();
        if (!localDate.isLeapYear() && month == 2) {
            maxDay--;
        }
        LocalDateTime begin = LocalDateTime.of(LocalDate.of(year, month, 1), LocalTime.MIN);
        LocalDateTime end = LocalDateTime.of(LocalDate.of(year, month, maxDay), LocalTime.MAX);
        String hql = "select sum(total) from ImportProduct where updateTime >= ? and updateTime <= ? and product.category.id = ?";
        Query query = getCurrentSession().createQuery(hql);
        query.setParameter(0, begin);
        query.setParameter(1, end);
        query.setParameter(2, categoryId);
        Double result = (Double)query.uniqueResult();
//        return result == null ? 0 :  result.doubleValue();
        return result == null ? 0 : Double.parseDouble(df.format(result));
    }
}
